package com.example.qxfw.ybzz.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.qx.ForecastInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.sql.Timestamp;
import java.util.List;

@Mapper
@DS("qx")
public interface ForecastInfoMapper extends BaseMapper<ForecastInfo> {

    @Select("select datechar, TimeChar, NTimes, StationID, StationName, Weather1, Weather2, cast(cast(MaxTemp as real)/1 As Decimal(10, 1)) MaxTemp, cast(cast(MinTemp as real)/1 As Decimal(10, 1)) MinTemp, WindD1, WindD2, Wind1 , Wind2 " +
            "from forecastinfo  " +
            "where datechar = '${dateChar}' and TimeChar = '${timeChar}' and city like CONCAT(#{city},'%')  " +
            "order by NTimes,StationId")
    List<ForecastInfo> searchForecastInfo(String dateChar, String timeChar, String  city);

    @Select("SELECT stationid, stationname, datechar, timechar, ntimes, maxtemp, mintemp, weather1, weather2, windd1, windd2, wind1, wind2  " +
            "FROM forecastinfo " +
            "WHERE datechar = #{dateChar} " +
            "AND timechar = #{timeChar} " +
            "AND city = #{city}")
    List<ForecastInfo> newGetForecast(@Param("dateChar") Timestamp dateChar,@Param("timeChar") String timeChar,@Param("city") String  city);

    @Select("select distinct county,StationId,StationName " +
            "from forecastinfo where city like #{city}")
    List<ForecastInfo> searchForecastInfo1(String city);

    @Select("select longitude, latitude, stationid, stationname, areaname, weather1, weather2, mintemp, maxtemp, windd1, wind1, windd2, wind2 " +
            "from forecastinfo " +
            "where datechar = #{dateChar} and timechar= #{timeChar} and ntimes = #{nTimes} and city like concat(#{city},'%') ")
    List<ForecastInfo> queryForecastInfo(@Param("dateChar") Timestamp dateChar, @Param("timeChar") String timeChar, @Param("nTimes") Integer nTimes, @Param("city") String city);

    @Select("select datechar " +
            "from forecastinfo " +
            "where TimeChar = #{timeChar} and city like '${city}%' " +
            "order by datechar desc limit 1")
    String selectMaxTime1(String timeChar, String city);

    @Select("select  datechar, timechar " +
            "from forecastinfo " +
            "order by datechar desc,timechar desc " +
            "limit 1 ")
    List<ForecastInfo> selectMaxTime();

    @Select("select timechar " +
            "from forecastinfo " +
            "where datechar='${dateChar}' and TimeChar='20' and StationID='${stationId}'")
    List<String> selectTimeChar(String dateChar, String stationId);

    @Select("select StationID, stationname, (cast(Weather1 as varchar(2)) ||' '|| cast(Weather2 as varchar(2))) as title, CAST(cast(case when maxtemp =-65535 then 0 else maxtemp end as float) AS NUMERIC(10, 0)) as maxtemp, CAST(cast(case when mintemp =-65535 then 0 else mintemp end as float) AS NUMERIC(10, 0)) as mintemp, case when wind1 =-65535 then 0 else wind1 end as wind1, case when wind2 =-65535 then 0 else wind2 end as wind2, case when windd1 =-65535 then 0 else windd1 end AS windd1, case when windd2 =-65535 then 0 else windd2 end AS windd2 " +
            "from forecastinfo " +
            "where datechar='${dateChar}' and TimeChar='${timeChar}' and NTimes='${nTimes}' and StationID = '${stationId}' ")
    List<ForecastInfo> selectList(String dateChar,String timeChar,String nTimes,String stationId);

    @Select("select weather1,weather2 " +
            "from forecastinfo " +
            "where datechar='${time}' and timeChar='20' and StationID='${stationId}' ")
    List<ForecastInfo> selectList1(String time, String stationId);

    @Select("select StationId, StationName, Weather1, Weather2, Wind1, Wind2, WindD1, WindD2, MinTemp, MaxTemp, nTimes " +
            "from forecastinfo " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and city like CONCAT(#{city},'%') ")
    List<ForecastInfo> selectList2(String dateChar, String timeChar, String  city);

    @Select("select rain " +
            "from forecastinfo " +
            "where dateChar between '${time1}' and '${time2}'and StationID='${stationId}' and to_char(dateChar,'MI')='00' and rain!='-65535' ")
    List<String> selectRain(String time1,String time2,String stationId);

    @Select("select dryBulbTemp " +
            "from forecastinfo " +
            "where dateChar between '${time1}' and '${time2}'and StationID='${stationId}' and DryBulbTemp!='-65535' ")
    List<String> selectTemp(String time1,String time2,String stationId);

    @Select("select (MaxTemp%2bMinTemp)/2  as maxtemp " +
            "from forecastinfo " +
            "where dateChar = ${time}' and StationId = '${stationId}' and maxtemp!='-65535' and mintemp!='-65535' " )
    List<String> selectMaxTemp(String time,String stationId);

    @Select("select WindVelocity " +
            "from forecastinfo " +
            "where dateChar between '${time1}' and '${time2}'and StationID='${stationId}' and WindVelocity!='-65535' ")
    List<String> selectWindVelocity(String time1,String time2,String stationId);

    @Select("select max(MaxTemp) as maxtemp " +
            "from forecastinfo " +
            "where dateChar ='${time}' and timeChar='20' and StationID='${stationId}' and maxtemp!='-65535' ")
    List<String> selectMaxTemp1(String time,String stationId);

    @Select("select MinTemp " +
            "from forecastinfo " +
            "where dateChar between '${time1}' and '${time2}'and StationID='${stationId}' and mintemp!='-65535' ")
    List<String> selectMinTemp(String time1,String time2,String stationId);

    @Select("select min(MinTemp) as mintemp " +
            "from forecastinfo " +
            "where dateChar='${time}' and timeChar='20' and StationID='${stationId}'and  mintemp !='-65535'")
    String selectMinTemp1(String time,String stationId);

    @Select("select max(wind1)wind1,max(wind2)wind2 " +
            "from forecastinfo " +
            "where dateChar='${time}' and timeChar='20' and StationID='${stationId}'and wind1!='-65535' and wind2!='-65535'")
    List<ForecastInfo> selectMaxWind(String time,String stationId);

    @Select("select sum(sumvalue) as sumvalue, (year||'-'||month||'-'||day ) as year " +
            "from forecastinfo " +
            "where stationid = '${stationId}'AND  to_timestamp('year||'-'||month||'-'||day','yyyy-MM-dd hh24:mi:ss')  BETWEEN '${time}' AND dateChar and sumvalue!='-65535' ")
    List<ForecastInfo> selectSumValue(String stationId,String time);

    @Select("select distinct county,StationId,StationName " +
            "from forecastinfo " +
            "where city like '${city}%'")
    List<ForecastInfo> selectStationInfo(String city);

}
